﻿using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Ortodoctor.modelo
{
    public class modeloErros : conexao
    {
        public modeloErros()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public string Verificar()
        {
            int faltantes = 0;
            try
            {
                String sql1 = "select max(id_endereco) from endereco";
                NpgsqlCommand command = new NpgsqlCommand(sql1, conn);
                Int32 max_end = (int.Parse(command.ExecuteScalar().ToString()));

                String sql2 = "select count(id_endereco) from endereco";
                NpgsqlCommand command2 = new NpgsqlCommand(sql2, conn);
                Int32 num_inseridos = (int.Parse(command2.ExecuteScalar().ToString()));

                faltantes = max_end - num_inseridos - 131;  //endereco começa a partir do 131
                if (faltantes < 0) { faltantes = 0; }
                return faltantes.ToString();
            }
            finally
            {
                conn.Close();
            }
        }

        public static string getCidade()
        {
            ModeloClinica client = new ModeloClinica();
            DataTable dt = client.getClinica(Convert.ToInt32(HttpContext.Current.Session["ID_CLINICA"]));
            string retorno = "";

            if (dt.Rows.Count > 0)
            {
                retorno += dt.Rows[0]["cidade"].ToString();
            }

            return retorno;
        }

        public string Corrigir()
        {
            int corrigidos = 0;
            int maior_id =131;

            try
            {
                String sql1 = "select max(id_endereco) from endereco";
                NpgsqlCommand command = new NpgsqlCommand(sql1, conn);
                maior_id = (int.Parse(command.ExecuteScalar().ToString()));

                for (int i = 131; i < maior_id; ++i)
                {
                    String sql = "select id_endereco from endereco where id_endereco = "+i;
                    NpgsqlCommand comm = new NpgsqlCommand(sql, conn);
                    NpgsqlDataReader sd = comm.ExecuteReader();

                    if (!sd.HasRows)
                    {
                        String sqlinert = "INSERT INTO endereco(id_endereco, cidade) VALUES (" + i + ",'NÃO INFORMADO');";
                        NpgsqlCommand comminsert = new NpgsqlCommand(sqlinert, conn);
                        Int32 rowsinserted = comminsert.ExecuteNonQuery();
                        corrigidos++;
                    }

                    sd.Close();
                }
            }
            finally
            {
                conn.Close();
            }
            return corrigidos.ToString();
        }
    }
}